package com.h2View.utils;

import com.h2View.db.entity.DataField;
import com.h2View.entity.DataType;
import com.h2View.exception.ServiceException;

import java.util.LinkedHashMap;
import java.util.List;
import java.util.Locale;

/**
 * @author 徐军文
 * @date 2021/7/2 22:37
 */
public class SqlUtil {

    private static DataType dataType = DataType.H2;

    public static DataType getDataType(){
        return dataType;
    }

    public static void setDataType(){
        dataType = CacheUtil.get("DataType");;
    }

    public static String getTableName(){
        switch (dataType.getDataType()){
            case 1:return "TABLE_NAME";
            case 2:return "tablename";
            default:return "";
        }
    }

    public static String getTablesColumns(String tableName){
        switch (dataType.getDataType()){
            case 1:return "show columns from " + tableName;
            case 2:return "SELECT a.attnum,\n" + "a.attname AS field,\n" + "t.typname AS type,\n"
                + "a.attlen AS length,\n" + "a.atttypmod AS lengthvar,\n" + "a.attnotnull AS notnull,\n"
                + "b.description AS comment\n" + "FROM pg_class c,\n" + "pg_attribute a\n"
                + "LEFT OUTER JOIN pg_description b ON a.attrelid=b.objoid AND a.attnum = b.objsubid,\n" + "pg_type t\n"
                + "WHERE c.relname = '"+tableName+"'\n" + "and a.attnum > 0\n" + "and a.attrelid = c.oid\n"
                + "and a.atttypid = t.oid\n" + "ORDER BY a.attnum;";
            default:return "";
        }
    }


    public static String getAllTablesSql(){
        if(dataType!=null){
            switch (dataType.getDataType()){
                case 1:return "show tables";
                case 2:return "SELECT   tablename   FROM   pg_tables \n" + "WHERE   tablename   NOT   LIKE   'pg%'\n"
                    + "AND tablename NOT LIKE 'sql_%' \n" + " ORDER   BY   tablename;";
                default:return "";
            }
        }
        throw new ServiceException("未找到DataType");
    }

    public static String simpleCreataTable(String tableName, List<DataField> dataFields){
        if(StringUtil.isEmpty(tableName)){
            throw new ServiceException(ServiceException.ERROR,"tableName不能为空！");
        }
        StringBuilder stb = new StringBuilder();
        stb.append("create table ").append(tableName).append("(");
        for(DataField dataField:dataFields){
            stb.append(dataField.getName()).append(" ").append(dataField.getType()).append(",");
        }
        stb.delete(stb.length()-1,stb.length());
        stb.append(")");
        return stb.toString().toUpperCase(Locale.ROOT);
    }

    public static String dropTable(String tableName){
        if(StringUtil.isEmpty(tableName)){
            throw new ServiceException(ServiceException.ERROR,"tableName不能为空！");
        }
        return ("drop table " + tableName).toUpperCase(Locale.ROOT);
    }

    public static String deleteSql(String tableName,LinkedHashMap<String,Object> entry){
        StringBuilder sql = new StringBuilder("delete from " + tableName + " where ");
        for(String key:entry.keySet()){
            Object value = entry.get(key);
            if(value instanceof Integer){
                sql.append(" ").append(key).append(" = ").append(value).append(" and");
            }else{
                sql.append(" ").append(key).append(" = ").append("'").append(value).append("'").append(" and");
            }
        }
        ToolUtil.deleteLast(sql,3);
        return sql.toString();
    }

    public static String insertSql(String tableName,LinkedHashMap<String,Object> entry){
        StringBuilder sql = new StringBuilder("insert into " + tableName + "(");
        for(String key:entry.keySet()){
            sql.append(key).append(",");
        }
        ToolUtil.deleteLast(sql);
        sql.append(") VALUES (");
        for(String key:entry.keySet()){
            Object value = entry.get(key);
            if(value instanceof Integer){
                sql.append(value).append(",");
            }else{
                sql.append("'").append(value).append("'").append(",");
            }
        }
        ToolUtil.deleteLast(sql);
        sql.append(");");
        return sql.toString();
    }

}
